#!/bin/ksh

DATABASE=omdb
proxyhost=10.61.99.95
proxyport=3128
export DATABASE
export proxyhost
export proxyport

SNAME=dbperf_agent_nr.ksh
if [ -f ${HOME}/sqllib/db2profile ]; then
    . ${HOME}/sqllib/db2profile
fi

PATH=${PATH}:/usr/local/bin:/usr/openv/netbackup:/usr/openv/netbackup/bin:${HOME}/utils
export PATH
COUNT=0
SECONDS=60

while getopts s:d: name
do
    case $name in
    d)    SFLAG=1
          DATABASE="$OPTARG";;
    [?])   printf "Usage: %s: [-d DATABASE] args\n" $0
          exit 2;;
    esac
done


db2 "connect to $DATABASE"
RC=$?
if [ $RC == 0 ]; then
    echo "Connected "
else
    echo "Not Connected - Exiting"
    exit
fi

HOST=$(hostname --short)

##########################################################################
##  Initialize Variables                                                ##
##########################################################################

COUNT=1      # Initializing the counter for the timer
typeset -i TRANSACTIONS_PER_SECOND
typeset -i READS_PER_SECOND
typeset -i DATA_WRITES_PER_SECOND
typeset -i INDEX_WRITES_PER_SECOND
typeset -i ROWS_READ_PER_SECOND
typeset -i ROWS_INSERTED_PER_SECOND
typeset -i ROWS_DELETED_PER_SECOND
typeset -i LOCK_WAITS_PER_SECOND
typeset -i LOCKS_WAITING_PER_SECOND
typeset -i RESPONSE_TIME_PER_SECOND
typeset -i TOTAL_STATEMENTS_PER_SECOND
typeset -i TOTAL_CONNECTIONS
typeset -i TOTAL_EXECUTING_CONNECTIONS
typeset -i USED_DISK_PERCENT_DB
typeset -i USED_DISK_PERCENT_BACKUP

TOTAL_STATEMENTS_SAVE=0
RESPONSE_TIME_SAVE=0
LOCKS_WAITING_SAVE=0
LOCK_WAITS_SAVE=0
ROWS_DELETED_SAVE=0
ROWS_INSERTED_SAVE=0
ROWS_READ_SAVE=0
TRANSACTIONS_SAVE=0
INTERVAL_SECONDS=60
READS_SAVE=0
INDEX_WRITES_SAVE=0
DATA_WRITES_SAVE=0
FETCHROWS="40"
SECONDS=10
SNAPTIME_SAVE="0"
STOPCOUNT=2880

SQL_TEXT="SELECT  COMMIT_SQL_STMTS, POOL_DATA_L_READS , POOL_DATA_WRITES,  POOL_INDEX_L_READS, POOL_INDEX_WRITES, APPLS_CUR_CONS, APPLS_IN_DB2 ,ROWS_DELETED, ROWS_READ, ROWS_SELECTED, ROWS_INSERTED, LOCK_WAITS, LOCKS_WAITING , DYNAMIC_SQL_STMTS, STATIC_SQL_STMTS, FAILED_SQL_STMTS , (((ELAPSED_EXEC_TIME_S * 1000000) + ELAPSED_EXEC_TIME_MS) / 1000000) as RESPONSE_TIME, ( DYNAMIC_SQL_STMTS + STATIC_SQL_STMTS + FAILED_SQL_STMTS ) as TOTAL_STATEMENTS FROM TABLE(SNAP_GET_DB_V97('', -2)) AS T "

############### 
## Main Loop ##
###############

until [ $COUNT -ge $STOPCOUNT ]
do

db2 "connect to $DATABASE"
RC=$?
if [ $RC == 0 ]; then
    echo "Connected "
else
    echo "Not Connected - Exiting"
    exit
fi

db2 -x "$SQL_TEXT" | while read TRANSACTIONS READS DATA_WRITES INDEX_READS INDEX_WRITES CONNECTIONS EXECUTING ROWS_DELETED ROWS_READ ROWS_SELECTED ROWS_INSERTED LOCK_WAITS LOCKS_WAITING DYNAMIC_SQL_STMTS STATIC_SQL_STMTS FAILED_SQL_STMTS RESPONSE_TIME TOTAL_STATEMENTS ; do
    clear
    
    SNAPTIME_SAVE=$SNAPTIME
    TRANSACTIONS_DIFFERENCE=$(( $TRANSACTIONS - $TRANSACTIONS_SAVE ))
    TRANSACTIONS_PER_SECOND=$(( $TRANSACTIONS_DIFFERENCE / $INTERVAL_SECONDS ))
    TRANSACTIONS_SAVE=$TRANSACTIONS
    READS_DIFFERENCE=$(( $READS - $READS_SAVE ))
    READS_PER_SECOND=$(( $READS_DIFFERENCE / $INTERVAL_SECONDS ))
    READS_SAVE=$READS
    DATA_WRITES_DIFF=$(( $DATA_WRITES - $DATA_WRITES_SAVE ))
    DATA_WRITES_PER_SECOND=$(( $DATA_WRITES_DIFF / $INTERVAL_SECONDS ))
    DATA_WRITES_SAVE=$DATA_WRITES
    INDEX_WRITES_DIFF=$(( $INDEX_WRITES - $INDEX_WRITES_SAVE ))
    INDEX_WRITES_PER_SECOND=$(( $INDEX_WRITES_DIFF / $INTERVAL_SECONDS ))
    INDEX_WRITES_SAVE=$INDEX_WRITES
    ROWS_READ_DIFF=$(( $ROWS_READ - $ROWS_READ_SAVE ))
    ROWS_READ_PER_SECOND=$(( $ROWS_READ_DIFF / $INTERVAL_SECONDS ))
    ROWS_READ_SAVE=$ROWS_READ
    ROWS_INSERTED_DIFF=$(( $ROWS_INSERTED - $ROWS_INSERTED_SAVE ))
    ROWS_INSERTED_PER_SECOND=$(( $ROWS_INSERTED_DIFF / $INTERVAL_SECONDS ))
    ROWS_INSERTED_SAVE=$ROWS_INSERTED
    ROWS_DELETED_DIFF=$(( $ROWS_DELETED - $ROWS_DELETED_SAVE ))
    ROWS_DELETED_PER_SECOND=$(( $ROWS_DELETED_DIFF / $INTERVAL_SECONDS ))
    ROWS_DELETED_SAVE=$ROWS_DELETED
    LOCK_WAITS_DIFF=$(( $LOCK_WAITS - $LOCK_WAITS_SAVE ))
    LOCK_WAITS_PER_SECOND=$(( $LOCK_WAITS_DIFF / $INTERVAL_SECONDS ))
    LOCK_WAITS_SAVE=$LOCK_WAITS
    LOCKS_WAITING_DIFF=$(( $LOCKS_WAITING - $LOCKS_WAITING_SAVE ))
    LOCKS_WAITING_PER_SECOND=$(( $LOCKS_WAITING_DIFF / $INTERVAL_SECONDS ))
    LOCKS_WAITING_SAVE=$LOCKS_WAITING
    TOTAL_STATEMENTS_DIFF=$(( $TOTAL_STATEMENTS - $TOTAL_STATEMENTS_SAVE ))
    TOTAL_STATEMENTS_PER_SECOND=$(( $TOTAL_STATEMENTS_DIFF / $INTERVAL_SECONDS ))
    TOTAL_STATEMENTS_SAVE=$TOTAL_STATEMENTS
    TOTAL_STATEMENTS_DIFF=$(( $TOTAL_STATEMENTS_DIFF + 1 ))
    TOTAL_CONNECTIONS=$CONNECTIONS
    TOTAL_EXECUTING_CONNECTIONS=$EXECUTING

done

if [[ $COUNT  -gt 1 ]]; then

curl -x $proxyhost:$proxyport https://collector.newrelic.com\
    -vi https://collector.newrelic.com/platform/v1/metrics \
    -H "X-License-Key: 9896f275d5c00cfc8e4bca2626846900bd4cbb8e" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -X POST -d '{
      "agent": {
        "host" : "db.internal.INSTANCE",
        "pid" : 1234,
        "version" : "1.0.0"
      },
      "components": [
        {
          "name": "'$HOST'",
          "guid": "com.ibm.db2",
          "duration" : 60,
          "metrics" : {
            "Component/'$DATABASE'[Transactions/Second]": '$TRANSACTIONS_PER_SECOND',
            "Component/'$DATABASE'[Pool_Reads/Second]": '$READS_PER_SECOND',
            "Component/'$DATABASE'[Data_Writes/Second]": '$DATA_WRITES_PER_SECOND',
            "Component/'$DATABASE'[Index_Writes/Second]": '$INDEX_WRITES_PER_SECOND',
            "Component/'$DATABASE'[Connections]": '$TOTAL_CONNECTIONS',
            "Component/'$DATABASE'[Applications_Executing]": '$TOTAL_EXECUTING_CONNECTIONS',
            "Component/'$DATABASE'[Rows_Read/Second]": '$ROWS_READ_PER_SECOND',
            "Component/'$DATABASE'[Rows_Inserted/Second]": '$ROWS_INSERTED_PER_SECOND',
            "Component/'$DATABASE'[Rows_Deleted/Second]": '$ROWS_DELETED_PER_SECOND',
            "Component/'$DATABASE'[Lock_Waits/Second]": '$LOCK_WAITS_PER_SECOND',
            "Component/'$DATABASE'[Threads_Waiting_Locks/Second]": '$LOCKS_WAITING_PER_SECOND',
            "Component/'$DATABASE'[SQL_Statements/Second]": '$TOTAL_STATEMENTS_PER_SECOND'
          }
        }
      ]
    }'


fi

sleep $INTERVAL_SECONDS
COUNT=$(( $COUNT + 1 ))
done

